package com.chinasofti.dao;

import com.chinasofti.domain.User;
import com.chinasofti.util.JDBCtils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;


public class UserDaolmpl{

    public User findByLoginNameAndPassword(String loginName,String password){
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;
        try{
            //1.创建连接
            conn = JDBCtils.getConnection();
            //创建操作sql对象
            String sql = "SELECT * FROM user WHERE loginname=? AND password =?";
            pstm = conn.prepareStatement(sql);
            //设置参数
            pstm.setString(1,loginName);
            pstm.setString(2,password);
            //执行sql语句，获取结果集
            rs = pstm.executeQuery();
            while(rs.next()){
                user = new User();
                user.setUid(rs.getString("uid"));
                user.setUcode(rs.getString("ucode"));
                user.setLoginname(rs.getString("loginname"));
                user.setPassword(rs.getString("password"));
                user.setUsername(rs.getString("username"));
                user.setGender(rs.getString("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setDutydate(rs.getDate("dutydate"));

            }

            return user;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCtils.close(conn,pstm,rs);
        }
        return null;
    }
    //练习 prepareStatement
    //findAll()      findById     insert(User user)
    //update(User user)   delete(Integer sid)

    public int delete(String uid){
        Connection conn = null;
        PreparedStatement pstm = null;
        int result = 0;
        try{
            //1.创建连接
            conn = JDBCtils.getConnection();
            //创建操作sql对象
            String sql = "delete  from user where uid=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,uid);
            //执行sql语句，获取结果集
            result = pstm.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            //throw new RuntimeException("查询失败");
        }finally {
            JDBCtils.close(conn,pstm);
        }
        return result;
    }

    public int update(User user){
        Connection conn = null;
        PreparedStatement pstm = null;
        int result = 0;
        try{
            //1.创建连接
            conn = JDBCtils.getConnection();
            //创建操作sql对象
            String sql = "UPDATE user set loginname = ? ,password=?, gender=? where uid=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,user.getLoginname());
            pstm.setString(2,user.getPassword());
            pstm.setString(3,user.getGender());
            pstm.setString(4,user.getUid());
            //执行sql语句，获取结果集
            result = pstm.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            //throw new RuntimeException("查询失败");
        }finally {
            JDBCtils.close(conn,pstm);
        }
        return result;
    }
    public int insert(User user){
        Connection conn = null;
        PreparedStatement pstm = null;
        int result = 0;
        try{
            //1.创建连接
            conn = JDBCtils.getConnection();
            //创建操作sql对象
            String sql = "INSERT INTO user VALUES(?,?,?,?,?,?,?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,user.getUid());
            pstm.setString(2,user.getUcode());
            pstm.setString(3,user.getLoginname());
            pstm.setString(4,user.getPassword());
            pstm.setString(5,user.getUsername());
            pstm.setString(6,user.getGender());
            pstm.setDate(7, user.getBirthday());
            pstm.setDate(8, user.getDutydate());
            //执行sql语句，获取结果集
            result = pstm.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            //throw new RuntimeException("查询失败");
        }finally {
            JDBCtils.close(conn,pstm);
        }
        return result;
    }

    public User findById(String uid) {
        ArrayList<User> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;
        try{
            //1.创建连接
            conn = JDBCtils.getConnection();
            //创建操作sql对象
            String sql = "SELECT * FROM user where uid = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,uid);
            //执行sql语句，获取结果集
            rs = pstm.executeQuery();
            while(rs.next()){
                user = new User();
                user.setUid(rs.getString("uid"));
                user.setUcode(rs.getString("ucode"));
                user.setLoginname(rs.getString("loginname"));
                user.setPassword(rs.getString("password"));
                user.setUsername(rs.getString("username"));
                user.setGender(rs.getString("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setDutydate(rs.getDate("dutydate"));

            }
            return user;
        }catch (Exception e){
            throw new RuntimeException("查询失败");
        }finally {
            JDBCtils.close(conn,pstm);
        }
    }


    public ArrayList<User> findAll() {
        ArrayList<User> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;
        try{
            //1.创建连接
            conn = JDBCtils.getConnection();
            //创建操作sql对象
            String sql = "SELECT * FROM user";
            pstm = conn.prepareStatement(sql);
            //执行sql语句，获取结果集
            rs = pstm.executeQuery();
            while(rs.next()){
                user = new User();
                user.setUid(rs.getString("uid"));
                user.setUcode(rs.getString("ucode"));
                user.setLoginname(rs.getString("loginname"));
                user.setPassword(rs.getString("password"));
                user.setUsername(rs.getString("username"));
                user.setGender(rs.getString("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setDutydate(rs.getDate("dutydate"));
                list.add(user);
            }
            return list;
        }catch (Exception e){
            throw new RuntimeException("查询失败");
        }finally {
            JDBCtils.close(conn,pstm);
        }
    }





}